﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace SQLTest
{
    public class TestStruct
    {
        [DbSetting(IsNotNull = true, DefaultValue = "'Default_AssetID'")]
        public string AssetID;

        [DbSetting(DefaultValue = "'Asset_Unknow'")]
        public string AssetName;

        [DbSetting(DefaultValue = 0,IsPrimary = true,IsNotNull =true)]
        public uint AssetCRC32;

        [DbSetting(DefaultValue = 0, IsUseType = true,dbType ="INTEGER")]
        public int AssetType;

        [DbSetting(DefaultValue = "NewID()", IsUseType = true, dbType = "UNIQUEIDENTIFIER" ,IsNotNull = true)]
        public Guid AssetGUID;

        [DbSetting(DefaultValue = "GETDATE()")]
        public DateTime AssetInLibDate;
    }

    class Program
    {
        static void Main(string[] args)
        {
            //Test();
            //TestBatchUpdate();

            //TestMySQL();

            TestSQLUtilies();

            Console.ReadKey();
        }

        static void TestSQLUtilies()
        {
            string[] lines = typeof(TestStruct).GetCreateTableSQLCmd();
            foreach(string line in lines)
            {
                Console.WriteLine(line);
            }

            MSSQLHelper helper = new MSSQLHelper("Data Source=ADIA1747;Initial Catalog=testdb;User ID=test;Password=123321");

            helper.CreateTable("Asset_Test", typeof(TestStruct));
        }

        static void TestMySQL()
        {
            //MySQLHelper mySQL = new MySQLHelper(@"server=localhost;user=root;database=testdb;port=3306;password=123321");
            MySQLHelper mySQL = new MySQLHelper(@"Data Source=localhost;Initial Catalog=testdb;User ID=root;Password=123321");

            Dictionary<string, object> item = new Dictionary<string, object>();
            item.Add("AssetID", "test002");
            item.Add("AssetName", "test002");
            item.Add("AssetCRC32", 1001);

            Dictionary<string, object> itemUpdate = new Dictionary<string, object>();
            item.Add("AssetType", 2);

            //mySQL.InsertToTable("Assets", item,null);
            mySQL.UpdateRow("Assets", item,"AssetID='test002'");

            //mySQL.DeleteTable("Assets");
        }

        /// <summary>
        /// 测试批量更新数据库
        /// </summary>
        static void TestBatchUpdate()
        {
            //MSSQLHelper helper = new MSSQLHelper("Data Source=192.168.10.7;Initial Catalog=test;User ID=liba;Password=123321");
            MSSQLHelper helper = new MSSQLHelper("Data Source=ADIA1747;Initial Catalog=testdb;User ID=test;Password=123321");

            Dictionary<string, object> UpdateItem = new Dictionary<string, object>()
            {
                {"test_id",123 },
                {"test_name","one six six" },
                {"test_info",DateTime.Now.ToString() },
            };

            Dictionary<string, object> UpdateItem2 = new Dictionary<string, object>()
            {
                {"test_id",222 },
                {"test_name","six x3" },
                {"test_info",DateTime.Now.ToString() },
            };

            helper.UpdateRow("test_table", new Dictionary<string, object>[] { UpdateItem, UpdateItem2 }, "test_id");
        }

        static void Test()
        {
            MSSQLHelper helper = new MSSQLHelper("Data Source=192.168.10.7;Initial Catalog=test;User ID=liba;Password=123321");

            Console.WriteLine("Try Connect to " + helper.Name);

            //helper.CreateTable("test_table_3", "id int,url text");
            //helper.DeleteTable("test_table_3");

            Dictionary<string, object> insertItem = new Dictionary<string, object>()
            {
                {"test_id",333 },
                {"test_name","three" },
                {"test_info",DateTime.Now.ToString() },
            };

            helper.InsertToTable("test_table", insertItem);

            Dictionary<string, object>[] rows = (Dictionary<string,object>[])helper.GetRows("test_table", "test_id>200");

            if (rows != null)
            {
                foreach (Dictionary<string, object> dict in rows)
                {
                    foreach (KeyValuePair<string, object> p in dict)
                    {
                        Console.WriteLine($"{p.Key} is {p.Value}");
                    }

                    Console.WriteLine(dict.GetKeys());
                    Console.WriteLine(dict.GetValues());

                    Console.WriteLine();
                }
            }

            Dictionary<string, object> UpdateItem = new Dictionary<string, object>()
            {
                {"test_name","one six six" },
                {"test_info",DateTime.Now.ToString() },
            };

            helper.UpdateRow("test_table", UpdateItem, "test_id=166");

            helper.DeleteRow("test_table", "test_id=333");
        }
    }
}
